<?php
//=======================================
//###################################
// Kayako Web Solutions
//
// Source Copyright 2001-2004 Kayako Web Solutions
// Unauthorized reproduction is not allowed
// License Number: $%LICENSE%$
// $Author: vshoor $ ($Date: 2006/04/11 20:58:15 $)
// $RCSfile: functions_impex.php,v $ : $Revision: 1.10 $ 
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//
//###################################
//=======================================


if (!defined("INSWIFT")) {
	trigger_error("Unable to process $PHP_SELF", E_USER_ERROR);
}

/**
* Loads the hash filename
*/
function loadImpexHash($hashfilename)
{
	global $_IMPEXHASH;

	$_IMPEXHASH = array();

	if (file_exists("./cache/".$hashfilename))
	{
		$hashdata = implode("", file("./cache/".$hashfilename));
		$hasharray = unserialize($hashdata);

		if (is_array($hasharray))
		{
			$_IMPEXHASH = $hasharray;
		}
	}
}

/**
* Save the Impex Hashname
*/
function saveImpexHash($hashfilename)
{
	global $_IMPEXHASH;

	if (!is_array($_IMPEXHASH))
	{
		$_IMPEXHASH = array();
	}

	$fp = fopen("./cache/".$hashfilename, "w+");
	fwrite($fp, serialize($_IMPEXHASH));
	fclose($fp);
	@chmod("./cache/".$hashfilename, 0666);

	return true;
}

/**
* Processes the variables that are slashed in eSupport v2
*/
function _impexProcessSlashVariables(&$data)
{
	foreach ($data as $key=>$val)
	{
		$data[$key] = stripslashes($val);
	}

	return true;
}

/**
* Rebuilds almost all caches
*/
function rebuildImpexCaches()
{
	require_once ("./modules/tickets/tickets.php");
	require_once ("./modules/knowledgebase/knowledgebase.php");
	require_once ("./modules/downloads/downloads.php");
	require_once ("./modules/troubleshooter/troubleshooter.php");
	require_once ("./modules/news/news.php");
	require_once ("./modules/teamwork/teamwork.php");

	require_once ("./includes/functions_comments.php");
	require_once ("./includes/functions_departments.php");
	require_once ("./includes/functions_staff.php");
	require_once ("./includes/functions_template.php");
	require_once ("./includes/functions_jscache.php");
	require_once ("./includes/functions_users.php");
	require_once ("./modules/knowledgebase/functions_knowledgebase.php");
	require_once ("./modules/parser/functions_parser.php");
	require_once ("./modules/tickets/functions_predefined.php");
	require_once ("./modules/tickets/functions_tickets.php");
	require_once ("./modules/tickets/functions_ticketcore.php");

	rebuildCommentCache();
	rebuildDepartmentCache();
	rebuildStaffCache();
	rebuildStaffGroupCache();
	rebuildGroupSettingsCache();
	rebuildStaffAssignCache();
	rebuildGroupAssignCache();
	rebuildTemplateGroupCache();
	rebuildUserGroupSettingsCache();
	rebuildKnowledgebaseJSCache();
	rebuildQueueCache();
	rebuildPredefinedJSCache();
	rebuildStatusCache();
	rebuildPriorityCache();
	rebuildTicketCountCache();
}

/**
* Converts an mysql-timestamp to a unix-timestamp.
*/
function returnUNIXTimestamp($timestamp)
{
	if (stristr($timestamp, "-"))
	{
		$timestamp = str_replace("-", "", str_replace(" ", "", str_replace(":", "", $timestamp)));
	}
	$year = substr($timestamp, 0, 4);
	$month = substr($timestamp, 4, 2);
	$day = substr($timestamp, 6, 2);
	$hour = substr($timestamp, 8, 2);
	$min = substr($timestamp, 10, 2);
	$sec = substr($timestamp, 12, 2);

	return mktime($hour, $min, $sec, $month, $day, $year);
}

/**
* Convert Perldesk Stamp
*/
function returnPerlDeskTimestamp($timestamp)
{
	$_stamp = explode("-", $timestamp);

	$month = $_stamp[1];
	$day = $_stamp[0];
	$year = $_stamp[2];
	$hour = substr($_stamp[3], 0, 2);
	$min = substr($_stamp[3], 3, 2);
	$sec = 0;

	return mktime($hour, $min, $sec, $month, $day, $year);
}

/**
* Convert SupportTrio Stamp
*/
function returnSupportTrioTimestamp($datestamp, $timestamp = 0)
{
	$_stamp = explode("-", $datestamp);

	$month = $_stamp[1];
	$day = $_stamp[2];
	$year = $_stamp[0];

	if (empty($year))
	{
		return time();
	}

	if (!empty($timestamp))
	{
		$_hourstamp = explode(":", $timestamp);
		$_hour = $_hourstamp[0];
		$_min = $_hourstamp[1];
		$_sec = $_hourstamp[2];
	} else {
		$_hour = $_min = $_sec = 0;
	}

	return mktime($_hour, $_min, $_sec, $month, $day, $year);
}

/**
* Insert a New Staff
*/
function _impexInsertStaff($fullname, $username, $password, $staffgroupid, $email, $mobilenumber, $signature)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."staff` (`fullname`, `username`, `password`, `staffgroupid`, `email`, `mobilenumber`, `ticketviewid`, `groupassigns`) VALUES ('". $dbCore->escape($fullname) ."', '". $dbCore->escape($username) ."', '". $dbCore->escape($password) ."', '". intval($staffgroupid) ."', '". $dbCore->escape($email) ."', '". $dbCore->escape($mobilenumber) ."', '1', '0');");
	$_staffid = $dbCore->lastInsertId();

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."signatures` (`dateline`, `staffid`, `signature`) VALUES ('". DATENOW ."', '". intval($_staffid) ."', '". $dbCore->escape($signature) ."');");

	$dbCore->query("INSERT INTO `".TABLE_PREFIX ."staffschedules` (`staffid`, `sunday_enabled`, `sunday_open`, `sunday_close`, `monday_enabled`, `monday_open`, `monday_close`, `tuesday_enabled`, `tuesday_open`, `tuesday_close`, `wednesday_enabled`, `wednesday_open`, `wednesday_close`, `thursday_enabled`, `thursday_open`, `thursday_close`, `friday_enabled`, `friday_open`, `friday_close`, `saturday_enabled`, `saturday_open`, `saturday_close`) VALUES ('". intval($_staffid) ."', '0', '00:00', '23:45', '1', '08:00', '17:00', '1', '08:00', '17:00', '1', '08:00', '17:00', '1', '08:00', '17:00', '1', '08:00', '17:00', '0', '00:00', '23:45');");

	return $_staffid;
}

/**
* Insert a new Department
*/
function _impexInsertDepartment($title, $departmenttype)
{
	global $dbCore, $departmentindex;

	if (empty($departmentindex))
	{
		$departmentindex = 2;
	}

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."departments` (`title`, `departmenttype`, `departmentmodule`, `displayorder`) VALUES ('". $dbCore->escape($title) ."', '". $dbCore->escape($departmenttype) ."', 'tickets', '". intval($departmentindex) ."');");
	$_departmentid = $dbCore->lastInsertId();

	$departmentindex++;

	// Assign this department to the primary template group
	_impexInsertTemplateGroupAssign($_departmentid, TGROUP_DEPARTMENT);

	return $_departmentid;
}

/**
* Insert a new Template Group Assignment
*/
function _impexInsertTemplateGroupAssign($toassignid, $type, $tgroupid = 1)
{
	global $dbCore;

	$dbCore->query("REPLACE INTO `". TABLE_PREFIX ."tgroupassigns` (`toassignid`, `type`, `tgroupid`) VALUES ('". intval($toassignid) ."', '". intval($type) ."', '". intval($tgroupid) ."');");

	return true;
}

/**
* Insert the Staff <> Department Link
*/
function _impexInsertStaffAssigns($departmentid, $staffid)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."staffassigns` (`departmentid`, `staffid`) VALUES ('". intval($departmentid) ."', '". intval($staffid) ."');");
	$_staffassignid = $dbCore->lastInsertId();

	return $_staffassignid;
}

/**
* Insert Email Queue
*/
function _impexInsertEmailQueue($email, $departmentid, $fetchtype, $host, $username, $userpassword, $customfromname, $customfromemail)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."emailqueues` (`email`, `type`, `fetchtype`, `host`, `port`, `username`, `userpassword`, `customfromname`, `customfromemail`, `priorityid`,`ticketstatusid`, `departmentid`, `prefix`, `ticketautoresponder`, `replyautoresponder`, `registrationrequired`, `tgroupid`) VALUES ('". $dbCore->escape($email) ."', 'tickets', '". iif($fetchtype=="pop3", "pop3", "pipe") ."', '". $dbCore->escape($host) ."', '". iif($fetchtype=="pop3", "110") ."', '". $dbCore->escape($username) ."', '". $dbCore->escape($userpassword) ."', '". $dbCore->escape($customfromname) ."', '". $dbCore->escape($customfromemail) ."', '1', '1', '". intval($departmentid) ."', '', '1', '0', '0', '1');");
	$_emailqueueid = $dbCore->lastInsertId();

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."queuesignatures` (`dateline`, `emailqueueid`, `contents`) VALUES ('". DATENOW ."', '". intval($_emailqueueid) ."', '');");

	return $_emailqueueid;
}

/**
* Insert KB Category
*/
function _impexInsertKBCategory($parentcategoryid, $title, $totalarticles, $displayorder)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."kbcategories` (`parentcategoryid`, `staffid`, `title`, `dateline`, `totalarticles`, `categorytype`, `displayorder`) VALUES ('". intval($parentcategoryid) ."', '1', '". $dbCore->escape($title) ."', '". DATENOW ."', '". intval($totalarticles) ."', 'public', '". intval($displayorder) ."');");
	$kbcategoryid = $dbCore->lastInsertId();

	_impexInsertTemplateGroupAssign($kbcategoryid, TGROUP_KNOWLEDGEBASE);

	return $kbcategoryid;
}

/**
* Updates the Parent Category for the given KB Category
*/
function _impexUpdateKBCategoryParent($kbcategoryid, $parentcategoryid)
{
	global $dbCore;

	$dbCore->query("UPDATE `". TABLE_PREFIX ."kbcategories` SET `parentcategoryid` = '". intval($parentcategoryid) ."' WHERE `kbcategoryid` = '". intval($kbcategoryid) ."';");

	return true;
}

/**
* Insert a new KB Article
*/
function _impexInsertKBArticle($subject, $contents, $views, $displayorder, $dateline, $ratinghits = 0, $ratingcount = 0)
{
	global $dbCore;

	if ($ratinghits == 0) {
		$_rating = "0";
	} else {
		$_rating = $ratingcount/$ratinghits;
	}
	if ($_rating >= 5)
	{
		$_rating = 5;
	}
	$_rating = round($_rating);

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."kbarticles` (`staffid`, `subject`, `isedited`, `editeddateline`, `editedstaffid`, `views`, `totalcomments`, `hasattachments`, `dateline`, `displayorder`, `articlestatus`, `articlerating`, `ratinghits`, `ratingcount`) VALUES ('1', '". $dbCore->escape($subject) ."', '0', '0', '0', '". intval($views) ."', '0', '0', '". intval($dateline) ."', '". intval($displayorder) ."', 'published', '". intval($_rating) ."', '". intval($ratinghits) ."', '". intval($ratingcount) ."');");
	$_kbarticleid = $dbCore->lastInsertId();
	if (!$_kbarticleid)
	{
		return false;
	}

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."kbarticledata` (`kbarticleid`, `contents`) VALUES ('". intval($_kbarticleid) ."', '". $dbCore->escape($contents) ."');");

	return $_kbarticleid;
}

/**
* Insert a new Category <> Article Link
*/
function _impexInsertKBArticleLink($kbcategoryid, $kbarticleid)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."kbarticlelinks` (`kbcategoryid`, `kbarticleid`) VALUES ('". intval($kbcategoryid) ."', '". intval($kbarticleid) ."');");

	return true;
}

/**
* Insert new Comment
*/
function _impexInsertComment($typeid, $commenttype, $fullname, $email, $contents, $isapproved, $dateline)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."comments` (`typeid`, `commenttype`, `fullname`, `email`, `isapproved`, `dateline`) VALUES ('". intval($typeid) ."', '". intval($commenttype) ."', '". $dbCore->escape($fullname) ."', '". $dbCore->escape($email) ."', '". intval($isapproved) ."', '". intval($dateline) ."');");
	$_commentid = $dbCore->lastInsertId();
	if (!$_commentid)
	{
		return false;
	}

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."commentdata` (`commentid`, `contents`) VALUES ('". intval($_commentid) ."', '". $dbCore->escape($contents) ."');");

	return $_commentid;
}

/**
* Insert a New Troubleshooter Cat
*/
function _impexInsertTroubleshooterCat($title, $description, $displayorder)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."troubleshootercat` (`staffid`, `title`, `description`, `dateline`, `displayorder`, `views`) VALUES ('1', '". $dbCore->escape($title) ."', '". $dbCore->escape($description) ."', '". DATENOW ."', '". intval($displayorder) ."', '0');");
	$troubleshootercatid = $dbCore->lastInsertId();

	_impexInsertTemplateGroupAssign($troubleshootercatid, TGROUP_TROUBLESHOOTER);

	return $troubleshootercatid;
}

/**
* Insert a new Troubleshooter Step
*/
function _impexInsertTroubleshooter($troubleshootercatid, $subject, $contents, $displayorder)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."troubleshootersteps` (troubleshootercatid, staffid, subject, dateline, edited, editedstaffid, displayorder, views, totalcomments) VALUES ('". intval($troubleshootercatid) ."', '1', '". $dbCore->escape($subject) ."', '". DATENOW ."', '0', '0', '". intval($displayorder) ."', '0', '0');");
	$troubleshooterid = $dbCore->lastInsertId();
	if (!$troubleshooterid)
	{
		return false;
	}

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."troubleshooterdata` (`troubleshooterid`, `contents`) VALUES ('". intval($troubleshooterid) ."', '". $dbCore->escape($contents) ."');");

	return $troubleshooterid;
}

/**
* Insert a link for the troubleshooter
*/
function _impexInsertTroubleshooterLink($troubleshootercatid, $parenttroubleshooterid, $childtroubleshooterid)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."troubleshooterlinks` (`troubleshootercatid`, `parenttroubleshooterid`, `childtroubleshooterid`) VALUES ('". intval($troubleshootercatid) ."', '". intval($parenttroubleshooterid) ."', '". intval($childtroubleshooterid) ."');");

	return true;
}

/**
* Insert News
*/
function _impexInsertNews($subject, $description, $contents, $dateline, $expiry)
{
	global $dbCore, $_IMPEXHASH;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."news` (`tgroupid`, `staffid`, `newstype`, `subject`, `emailsubject`, `description`, `dateline`, `expiry`, `edited`, `editedstaffid`, `editeddateline`, `totalcomments`) VALUES ('1', '1', 'public', '". $dbCore->escape($subject) ."', '". $dbCore->escape($subject) ."', '". $dbCore->escape($description) ."', '". intval($dateline) ."', '". intval($expiry) ."', '0', '0', '0', '0');");
	$newsid = $dbCore->lastInsertId();
	if (!$newsid)
	{
		return false;
	}

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."newsdata` (`newsid`, `contents`) VALUES ('". intval($newsid) ."', '". $dbCore->escape($contents) ."');");

	return $newsid;
}

/**
* Insert News Subscriber
*/
function _impexInsertSubscriber($email)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."newssubscribers` (`tgroupid`, `userid`, `email`, `dateline`, `validated`) VALUES ('1', '0', '". $dbCore->escape($email) ."', '". DATENOW ."', '1');");

	return true;
}

/**
* Insert a new Predefined Category
*/
function _impexInsertPredefinedCategory($parentcategoryid, $title)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."predefinedcategories` (`parentcategoryid`,`categorytype`, `staffid`, `title`) VALUES ('". intval($parentcategoryid) ."', '1', '0', '". $dbCore->escape($title) ."');");
	$predefinedcategoryid = $dbCore->lastInsertId();

	return $predefinedcategoryid;
}

/**
* Insert new Predefined Reply
*/
function _impexInsertPredefinedReply($predefinedcategoryid, $subject, $contents)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."predefinedreplies` (`predefinedcategoryid`, `staffid`, `subject`, `dateline`) VALUES ('". intval($predefinedcategoryid) ."', '0', '". $dbCore->escape($subject) ."', '". DATENOW ."');");
	$predefinedreplyid = $dbCore->lastInsertId();
	if (!$predefinedreplyid)
	{
		return false;
	}

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."predefinedreplydata` (`predefinedreplyid`, `contents`) VALUES ('". intval($predefinedreplyid) ."', '". $dbCore->escape($contents) ."');");

	return $predefinedreplyid;
}

/**
* Insert New Ticket
*/
function _impexInsertTicket($ticketmaskid, $departmentid, $ticketstatusid, $priorityid, $emailqueueid, $userid, $staffid, $ownerstaffid, $fullname, $email, $lastreplier, $subject, $dateline, $lastactivity, $totalreplies, $creator)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."tickets` (`ticketmaskid`, `departmentid`, `ticketstatusid`, `priorityid`, `emailqueueid`, `userid`, `staffid`, `ownerstaffid`, `assignstatus`, `fullname`, `email`, `lastreplier`, `subject`, `dateline`, `lastactivity`, `laststaffreplytime`, `totalreplies`, `creator`, `tgroupid`) VALUES ('". $dbCore->escape($ticketmaskid) ."', '". intval($departmentid) ."', '". intval($ticketstatusid) ."', '". intval($priorityid) ."', '". intval($emailqueueid) ."', '". intval($userid) ."', '". intval($staffid) ."', '". intval($ownerstaffid) ."', '". iif(!empty($ownerstaffid), "1", "0") ."', '". $dbCore->escape($fullname) ."', '". $dbCore->escape($email) ."', '". $dbCore->escape($lastreplier) ."', '". $dbCore->escape($subject) ."', '". intval($dateline) ."', '". intval($lastactivity) ."', '". intval($lastactivity) ."', '". intval($totalreplies) ."', '". intval($creator) ."', '1');");
	$ticketid = $dbCore->lastInsertId();

	return $ticketid;
}

/**
* Insert a new ticket post
*/
function _impexInsertTicketPost($ticketid, $dateline, $userid, $fullname, $email, $emailto, $subject, $ipaddress, $creator, $isemailed, $staffid, $contents)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."ticketposts` (`ticketid`, `dateline`, `userid`, `fullname`, `email`, `emailto`, `subject`, `ipaddress`, `creator`, `isemailed`, `staffid`, `contents`, `contenthash`, `subjecthash`) VALUES ('". intval($ticketid) ."', '". intval($dateline) ."', '". intval($userid) ."', '". $dbCore->escape($fullname) ."', '". $dbCore->escape($email) ."', '". $dbCore->escape($emailto) ."', '". $dbCore->escape($subject) ."', '". $dbCore->escape($ipaddress) ."', '". intval($creator) ."', '". intval($isemailed) ."', '". intval($staffid) ."', '". $dbCore->escape($contents) ."', '". md5($contents) ."', '". md5($subject) ."');");
	$ticketpostid = $dbCore->lastInsertId();

	return $ticketpostid;
}

/**
* Insert a new Ticket Note
*/
function _impexInsertTicketNote($typeid, $notetype, $forstaffid, $bystaffid, $dateline, $notes)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."ticketnotes` (`typeid`, `notetype`, `forstaffid`, `bystaffid`, `dateline`, `notes`) VALUES ('". intval($typeid) ."', '". intval($notetype) ."', '". intval($forstaffid) ."', '". intval($bystaffid) ."', '". intval($dateline) ."', '". $dbCore->escape($notes) ."');");
	$ticketnoteid = $dbCore->lastInsertId();

	return $ticketnoteid;
}

/**
* Insert a new User
*/
function _impexInsertUser($usergroupid, $fullname, $email, $password, $enabled, $allowemail)
{
	global $dbCore;

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."users` (usergroupid, fullname, userpassword, userpasswordtxt, dateline, enabled, loginapi_moduleid, loginapi_userid, languageid, useremailcount, allowemail) VALUES ('". intval($usergroupid) ."', '". $dbCore->escape($fullname) ."', '". md5($password) ."', '". $dbCore->escape($password) ."', '". DATENOW ."', '1', '1', '0', '1', '1', '1');");
	$userid = $dbCore->lastInsertId();
	if (!$userid)
	{
		return false;
	}

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."useremails` (`userid`, `email`, `isprimary`) VALUES ('". intval($userid) ."', '". $dbCore->escape($email) ."', '1');");

	return $userid;
}

/**
* Tag a ticket as has notes
*/
function _impexTagTicketHasNotes($ticketid)
{
	global $dbCore;

	$dbCore->query("UPDATE `". TABLE_PREFIX ."tickets` SET `hasnotes` = '1' WHERE `ticketid` = '". intval($ticketid) ."';");

	return true;
}

/**
* Tag a ticket where email is the specified email with the specified userid
*/
function _impexTagUserID($email, $userid)
{
	global $dbCore;

	$dbCore->query("UPDATE `". TABLE_PREFIX ."tickets` SET `userid` = '". intval($userid) ."' WHERE `email` = '". $dbCore->escape($email) ."';");

	return true;
}
?>